﻿using System;

namespace Dapper.Sharding
{
    internal class SqlServerQuery : IQuery
    {
        public SqlServerQuery(IDatabase db) : base(db)
        {

        }

        internal override void Init<T>(ITable<T> table, bool setField = true, bool setOrderby = true)
        {
            sqlTable.Append('[');
            sqlTable.Append(table.Name);

            if (string.IsNullOrEmpty(forUpdate))
            {
                sqlTable.Append("] WITH(NOLOCK)");
            }
            else
            {
                sqlTable.Append(']');
                sqlTable.Append(forUpdate);
            }

            if (setField)
            {
                returnFields = table.SqlField.AllFields;
            }

            if (setOrderby)
            {
                sqlOrderBy = " ORDER BY " + table.SqlField.PrimaryKey;
            }
        }

        internal override void Init<T>(ITable<T> table, string asName, bool setField = true, bool setOrderby = true)
        {
            sqlTable.Append('[');
            sqlTable.Append(table.Name);
            sqlTable.Append(']');
            sqlTable.Append(" AS ");
            sqlTable.Append(asName);

            if (string.IsNullOrEmpty(forUpdate))
            {
                sqlTable.Append(" WITH(NOLOCK)");
            }
            else
            {
                sqlTable.Append(forUpdate);
            }
            
            if (setField)
            {
                returnFields = asName + ".*";
            }

            if (setOrderby)
            {
                sqlOrderBy = " ORDER BY " + asName + "." + table.SqlField.PrimaryKey;
            }
        }

        public override IQuery InnerJoin<T>(ITable<T> table, string asName, string on)
        {
            sqlTable.Append(" INNER JOIN [");
            sqlTable.Append(table.Name);
            sqlTable.Append("] AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" WITH(NOLOCK) ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery LeftJoin<T>(ITable<T> table, string asName, string on)
        {
            sqlTable.Append(" LEFT JOIN [");
            sqlTable.Append(table.Name);
            sqlTable.Append("] AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" WITH(NOLOCK) ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery InnerJoin(string sql, string asName, string on)
        {
            sqlTable.Append(" INNER JOIN (");
            sqlTable.Append(sql);
            sqlTable.Append(") AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery LeftJoin(string sql, string asName, string on)
        {
            sqlTable.Append(" LEFT JOIN (");
            sqlTable.Append(sql);
            sqlTable.Append(") AS ");
            sqlTable.Append(asName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery InnerJoin(string tableName, string on)
        {
            sqlTable.Append(" INNER JOIN ");
            sqlTable.Append(tableName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override IQuery LeftJoin(string tableName, string on)
        {
            sqlTable.Append(" LEFT JOIN ");
            sqlTable.Append(tableName);
            sqlTable.Append(" ON ");
            sqlTable.Append(on);
            return this;
        }

        public override string Sql()
        {
            if (string.IsNullOrEmpty(returnFields))
            {
                returnFields = "*";
            }

            if (take == 0)
            {
                return $"SELECT{distinct} {returnFields} FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}";
            }
            else
            {
                if (skip == 0) //第一页,使用Top语句
                {
                    return $"SELECT{distinct} TOP {take} {returnFields} FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}";
                }
                else
                {
                    if (db.DbVersion == DataBaseVersion.SqlServer2012)
                    {
                        if (string.IsNullOrEmpty(sqlOrderBy))
                        {
                            throw new Exception("sqlserver IQuery must set OrderBy==>for OFFSET [skip] ROWS FETCH NEXT [take] ROWS ONLY");
                        }

                        return $"SELECT{distinct} {returnFields} FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY";
                    }
                    else
                    {
                        if (string.IsNullOrEmpty(sqlOrderBy))
                        {
                            throw new Exception("sqlserver IQuery must set OrderBy==>for ROW_NUMBER OVER(???)");
                        }

                        //使用ROW_NUMBER()
                        return $"SELECT{distinct} {returnFields} FROM (SELECT ROW_NUMBER() OVER({sqlOrderBy.Trim()}) AS Row_Number,{returnFields} FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}) A WHERE Row_Number BETWEEN {skip + 1} AND {skip + take}";
                    }
                }
            }
        }

        public override string SqlCount()
        {
            return $"SELECT COUNT(1) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        #region aggregate

        public override string SqlExists()
        {
            return $"SELECT TOP 1 1 FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}{sqlOrderBy}";
        }

        public override string SqlMin(string field)
        {
            return $"SELECT MIN({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlMax(string field)
        {
            return $"SELECT MAX({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlSum(string field)
        {
            return $"SELECT SUM({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        public override string SqlAvg(string field)
        {
            return $"SELECT AVG({field}) FROM {sqlTable}{sqlWhere}{sbWhere}{sqlGroupBy}{sqlHaving}";
        }

        #endregion
    }
}
